--    Author    : ChenErHao
--    Name      : ADM.RPT_BAL_DETAIL.HQL
--    Functions : 固定报表_贷款发生额明细
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-17  ChenErHao       1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE ADM.RPT_BAL_DETAIL PARTITION (DATA_DATE = '#V_DATA_DATE#',DATA_SRC_ORG) 
-- 存款合计
 SELECT
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(DEP_BALANCE) AS AMOUNT                                                   -- 存款余额
   ,'A01' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'D' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.DEPB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE
 
UNION ALL
-- （一）个人存款
 SELECT
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(DEP_BALANCE) AS AMOUNT                                                   -- 存款余额
   ,'A02' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'D' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.DEPB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'                                                -- 客户类型为1
   AND CUSTOMER_TYPE = '1'
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE
 
UNION ALL
--  其中:活期存款
--      定期存款
--      定活两便存款
--      通知存款
--      协议存款
--      协定存款
--      保证金存款
 SELECT
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                          -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(DEP_BALANCE) AS AMOUNT                                                   -- 存款余额
   ,CASE WHEN PRODUCT_CATEGORY='D013' THEN 'A03'
         WHEN PRODUCT_CATEGORY='D014' THEN 'A04'
         WHEN PRODUCT_CATEGORY='D02' THEN 'A05'
         WHEN PRODUCT_CATEGORY='D03' THEN 'A06'
         WHEN PRODUCT_CATEGORY IN ('D51','D52') THEN 'A08'
       ELSE 'A09'
    END AS SUBJECT_SEQ                                                           -- 指标序号
   ,'D' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.DEPB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND CUSTOMER_TYPE = '1'                                                       -- 客户类型为1
   AND PRODUCT_CATEGORY IN ('D013','D014','D02','D03','D04','D051','D052'
       ,'D061','D062','D063','D064','D065','D066','D067','D068','D069')          -- 产品类型
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,PRODUCT_CATEGORY
  ,T1.JRTJ_AREA_CODE
 
UNION ALL
-- （二）单位存款
 SELECT
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(DEP_BALANCE) AS AMOUNT                                                   -- 存款余额
   ,'A10' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'D' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.DEPB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND CUSTOMER_TYPE = '0'                                                       -- 客户类型为0
   AND PRODUCT_CATEGORY IN ('D011','D012','D03','D04','D051','D052','D061'
       ,'D062','D063','D064','D065','D066','D067','D068','D069')                 -- 产品类型
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE
 
UNION ALL
--  其中:活期存款
--      定期存款
--      定活两便存款
--      通知存款
--      协议存款
--      协定存款
--      保证金存款
 SELECT
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(DEP_BALANCE) AS AMOUNT                                                   -- 存款余额
   ,CASE WHEN PRODUCT_CATEGORY='D011' THEN 'A11'
         WHEN PRODUCT_CATEGORY='D012' THEN 'A12'
         WHEN PRODUCT_CATEGORY='D03' THEN 'A13'
         WHEN PRODUCT_CATEGORY='D04' THEN 'A14'
         WHEN PRODUCT_CATEGORY IN ('D51','D52') THEN 'A15'
       ELSE 'A16'
    END AS SUBJECT_SEQ                                                           -- 指标序号
   ,'D' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.DEPB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND CUSTOMER_TYPE = '0'                                                       -- 客户类型为1
   AND PRODUCT_CATEGORY IN ('D011','D012','D03','D04','D051','D052','D061'
       ,'D062','D063','D064','D065','D066','D067','D068','D069')                 -- 产品类型
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,PRODUCT_CATEGORY
  ,T1.JRTJ_AREA_CODE

UNION ALL
-- 贷款合计
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'A01' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'L' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND CUSTOMER_TYPE IN ('0','1')
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE
   
UNION ALL
-- （一）境内贷款
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'A02' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'L' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND LPIC_SUB <> '2'                                                           -- 行业不为2
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE
   
UNION ALL
-- 1、个人贷款
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'A03' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'L' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND LPIC_SUB = '1'                                                            -- 行业等于1
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE

UNION ALL
--   其中：个人消费贷款
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'A04' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'L' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND LPIC_SUB = '1'                                                            -- 行业等于1
   AND PRODUCT_CATEGORY IN ('F0211','F0212','F02131','F02132','F0219')           -- 产品类别
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE
 
UNION ALL
--       其中：住房贷款
--             汽车消费贷款
--             助学贷款
--             其他消费贷款
--        个人经营贷款
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,CASE WHEN PRODUCT_CATEGORY = 'F0211' THEN 'A05'
         WHEN PRODUCT_CATEGORY = 'F0212' THEN 'A06'
         WHEN PRODUCT_CATEGORY IN ('F02131','F02132') THEN 'A07'
         WHEN PRODUCT_CATEGORY = 'F0219' THEN 'A08'
         WHEN PRODUCT_CATEGORY = 'F022' THEN 'A09'
       ELSE ''
    END AS SUBJECT_SEQ                                                           -- 指标序号
   ,'L' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND LPIC_SUB = '1'                                                            -- 行业等于1
   AND PRODUCT_CATEGORY IN ('F0211','F0212','F02131','F02132','F0219','F022')    -- 产品类别
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,PRODUCT_CATEGORY
  ,T1.JRTJ_AREA_CODE
 
UNION ALL
-- 2、单位贷款
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'A10' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'L' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND LPIC_SUB <> '2'                                                           -- 行业不等于2
   AND CUSTOMER_TYPE = '0'                                                       -- 客户类型为0
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE
 
UNION ALL
--    其中：经营贷款
--          固定资产贷款
--          贸易融资
--          并购贷款
--          融资租赁
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,CASE WHEN PRODUCT_CATEGORY = 'F022' THEN 'A11'
         WHEN PRODUCT_CATEGORY = 'F023' THEN 'A12'
         WHEN PRODUCT_CATEGORY IN ('F081','F082') THEN 'A13'
         WHEN PRODUCT_CATEGORY = 'F12' THEN 'A14'
         WHEN PRODUCT_CATEGORY = 'F09' THEN 'A15'
       ELSE ''
    END AS SUBJECT_SEQ                                                         -- 指标序号
   ,'L' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND LPIC_SUB <> '2'                                                           -- 行业不等于2
   AND CUSTOMER_TYPE = '0'                                                       -- 客户类型为0
   AND PRODUCT_CATEGORY IN ('F022','F023','F081','F082','F12','F09')             -- 产品类别
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,PRODUCT_CATEGORY
  ,T1.JRTJ_AREA_CODE
 
UNION ALL
-- （二）境外贷款
 SELECT 
    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd HH:mm:ss') AS BATCH_DATE          -- 跑批日期
   ,DATA_ORG_TYPE                                                           -- 数据机构类型
   ,T1.JRTJ_AREA_CODE                                                              -- 集中系统地区编码
   ,SUM(LOAN_BALANCE) AS AMOUNT                                                  -- 贷款余额
   ,'A16' AS SUBJECT_SEQ                                                         -- 指标序号
   ,'L' AS FLAG                                                                  -- 存贷款标识 D-存款,L-贷款
   ,DATA_SRC_ORG                                                                 -- 上报机构(分区)
  FROM EDW.LOAB_INFO T1
 WHERE DATA_DATE ='#V_DATA_DATE#'
   AND LPIC_SUB = '2'                                                           -- 行业不等于2
 GROUP BY 
   DATA_ORG_TYPE
  ,DATA_SRC_ORG
  ,T1.JRTJ_AREA_CODE
;